package com.xksoft.dbAccessor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

public class DbReaderWriter {

	private Connection connection = null;

	private String url = "jdbc:postgresql://localhost/cafe";

	private Logger logger = Logger.getLogger(DbReaderWriter.class.getName());

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		new PushThread().start();
		new RetriveThread().start();
	}

	public DbReaderWriter() {

		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public synchronized void pushData(String arg) {
		long timestamp = System.currentTimeMillis();
		// System.out.println(arg + ": " + timestamp);

		Statement stat = null;
		String sql = "insert into alert_table(alert_id,sender,alert_info) values("
				+ timestamp + ",'COM1','" + arg + "')";

		try {
			connection = DriverManager.getConnection(url, "java", "123");
			stat = connection.createStatement();
			stat.executeUpdate(sql);
			logger.info("push: " + timestamp);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException exx) {
					exx.printStackTrace();
				}
			}
		}
	}

	public synchronized String retriveData() {
		String ret = null;
		Statement stat = null;
		ResultSet result = null;
		String sql = "select max(alert_id) from alert_table";
		try{
			connection = DriverManager.getConnection(url, "java", "123");
			stat = connection.createStatement();
			result = stat.executeQuery(sql);
			if(result.next()){
				ret = result.getString(1);
			}
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			if(result!=null){
				try{
					result.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(stat != null){
				try{
					stat.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(connection!=null){
				try{
					connection.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
		}
		return ret;
	}
}

class PushThread extends Thread {
	public void run() {
		while (true) {
			DbReaderWriter db = new DbReaderWriter();
			db.pushData("COM1 Alert");
			try {
				sleep(3000);
			} catch (InterruptedException ie) {
				ie.printStackTrace();
			}
		}

	}
}

class RetriveThread extends Thread{
	Logger logger = Logger.getLogger(RetriveThread.class.getName());
	public void run(){
		while(true){
			DbReaderWriter db = new DbReaderWriter();
			String data = db.retriveData();
			logger.info("retrive: " + data);
			try{
				sleep(3000);
			}catch(InterruptedException e){
				e.printStackTrace();
			}
		}
	}
}
